<%@ taglib uri="/tags/struts-html" prefix="html" %>
<%@ taglib uri="/tags/struts-bean" prefix="bean" %>
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*,deweb.cimaplanning.*,java.text.DecimalFormat"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="deweb.cimaplanning.misc"%>
<%@page import="java.util.ArrayList"%>
<%@page import="deweb.cimaplanning.beans.CostsElementsBean"%>
<%@page import="com.cpi.cornpp.costelement.dto.CostElementDTO"%>
<%@page import="com.cs.common.pool.DBDAO"%>
<%@page import="deweb.cimaplanning.beans.POCostsBean"%>
<%@page import="deweb.cimaplanning.dto.POCosts"%>
<%@page import="java.text.DecimalFormat"%>
<%@page import="deweb.cimaplanning.beans.PODistributionListPerPlantBean"%>
<%@page import="deweb.cimaplanning.dto.MonthsPeriodsVersions"%>
<%@page import="deweb.cimaplanning.beans.MonthsPeriodsVersionsBean"%>
<%@page import="deweb.cimaplanning.dto.PORecurrentCost"%>
<%@page import="deweb.cimaplanning.beans.PlantsVersionsMonthsBean"%>
<%@page import="deweb.cimaplanning.dto.PlantsVersionsMonths"%>
<%@page import="deweb.cimaplanning.beans.POPlantCostsBean"%>
<%@page import="deweb.cimaplanning.dto.POPlantCosts"%>
<%@page import="java.util.Hashtable"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<style>
#ajustslayer {
	position: relative;
	visibility: visible;
	width: 950px;
	height: 520px;
	background-color: #FFFFFF;
	border: 1px solid #CCCCCC;
	padding: 10px;
	overflow-x:scroll;
	overflow-y:scroll;
	overflow:moz-scrollbars-horizontal; 
}
</style>
<div id="ajustslayer" align="left">
	<table>
		<tr>
			<td align="left" class="titulo_lista_interna">
				
			</td>
		</tr>
		<tr>
	    	<td>
	    		<%
boolean DEBUG = false;
if (DEBUG) out.println("<br>DEBUG ON: [" + request.getRequestURI() + "]");

//bloque de contadores y elementos de ciclo
int i;
int j;
int k;

boolean odd;

DBDAO dbConn	= null;
Connection conn = null;
Statement stmt  = null;
ResultSet rs	= null;

String sql 		= null;


try{
	dbConn	= new DBDAO();
    conn	= dbConn.getConnection(misc.DS_NAME); 
    stmt	= conn.createStatement();
    
	sql = "";
	
	String keyId = session.getAttribute("versionId") == null ? "" : (String)session.getAttribute("versionId");
	
	if (DEBUG) {
		out.println("<br>keyId: [" + keyId + "]");
	} 
	
/*
	//version quemada para facilitar las cosas
	keyId = "2";
	out.println("<br>Version quemada para facilidad de desarrollo: [" + keyId + "]");
*/
	
	if (keyId.length() == 0){
		out.println("<br>Falta que se pase parametro de version");
		return;
	}

	int versionId;
	try {
		versionId = Integer.parseInt(keyId);
	} catch (Exception eParse) {
		versionId = -1;
		System.out.println("[cost_per_plant]: " + eParse.getMessage());
	}
	
	sql  = "SELECT version_id, short_name, description, cost ";
	sql += "	FROM vw_versions_processes_status ";
	sql += "	WHERE version_id = " + versionId + " ";
	
	if (DEBUG) out.println("<br>sql: [" + sql + "]");
	rs = stmt.executeQuery(sql);
	
	int cost = -1;
	String versionShortName = "";
	
	if (rs.next()){
		cost 				= rs.getInt("cost");
		versionShortName	= rs.getString("short_name");
	}
	
	if (cost < 0) {
		out.println("<br>Versión id [" + versionId + "] no existe");
		return;
	} else if (cost == 0){
		out.println("<br>No hay resultados de costos para versión '" + versionShortName + "', favor de 'Calcular Costos'");
		return;
	}
%>
<form name="mainFrm" method="POST">
<%
	//listar los elementos de costo SIN costo para que no hagan espacio extra
	sql  = "SELECT DISTINCT ce.short_name, cpo.cost_element_id ";
	sql += "	FROM costs_elements ce ";
	sql += "		LEFT OUTER JOIN costs_po cpo ";
	sql += "			ON ce.cost_element_id = cpo.cost_element_id ";
	sql += "		LEFT OUTER JOIN vw_v_purchases_orders_versions pov ";
	sql += "			ON cpo.purchase_order_version_id = pov.purchase_order_version_id ";
	sql += "			AND pov.version_id = " + versionId + " ";
	sql += "	WHERE cpo.cost_element_id IS NULL ";
	sql += "	ORDER BY ce.short_name ";
	
	if (DEBUG) out.println("<br>sql: [" + sql + "]");
	rs = stmt.executeQuery(sql);
	
	String costsElementsCodes = "";
	
	while (rs.next()){
		costsElementsCodes += "'" + rs.getString("short_name") + "', ";
	}

	int orderField		= 1;
	boolean ascending	= true;
	String filter		= "short_name NOT IN (" + costsElementsCodes
												+ "'" + misc.BUS_PER_TON
												+ "', '" + misc.USD_PER_TON
												+ "', '" + misc.WAREHOUSE_IN
												+ "', '" + misc.WAREHOUSE_MONTH
												+ "', '" + misc.WAREHOUSE_OUT
												+ "', '" + misc.FUTURES
												+ "', '" + misc.LOSS_FUTURES + "')";
	
	CostsElementsBean costsElementsBean = new CostsElementsBean();

	ArrayList aCostsElements		= costsElementsBean.browse(orderField, ascending, filter);
	CostElementDTO costElementDto	= null;
	
	//listado de elementos de costo recurrentes para la version
	sql  = "SELECT DISTINCT ce.short_name ";
	sql += "	FROM manual_recurrent_charges rc, vw_v_plants_versions pv, costs_elements ce ";
	sql += "	WHERE rc.plant_id = pv.plant_id ";
	sql += "		AND rc.month_period_version_id = pv.month_period_version_id ";
	sql += "		AND rc.cost_element_id = ce.cost_element_id ";
	sql += "		AND pv.version_id = " + versionId + " ";
	sql += "	ORDER BY ce.short_name ";
	
	if (DEBUG) out.println("<br>sql: [" + sql + "]");
	rs = stmt.executeQuery(sql);
	
	ArrayList aCostsElementsRecurrent 	= new ArrayList(25);
	PORecurrentCost poRecurrentDto		= null;
	String recurrentCostName;
	
	while (rs.next()){
		aCostsElementsRecurrent.add(rs.getString("short_name"));
	}
	
	//listado de los meses de la version
	MonthsPeriodsVersionsBean mpvBean	= new MonthsPeriodsVersionsBean();
	ArrayList aMonthPeriodsVersions 	= mpvBean.browse(versionId);
	MonthsPeriodsVersions mpvDto 		= null;
	
	//listado de las plants versions de la version
	PlantsVersionsMonthsBean pvBean		= new PlantsVersionsMonthsBean();
	ArrayList aPlantsVersions			= null;
	PlantsVersionsMonths pvDto			= null;
	
	//distribucion de PO por planta
	PODistributionListPerPlantBean pdBean 	= new PODistributionListPerPlantBean();
	ArrayList aPODistributions; 
	
	//lectura de las PO
	filter				= misc.USD_PER_TON;
	POCostsBean pocBean	= new POCostsBean();
	ArrayList aPOs		= pocBean.browse(versionId, filter, true);
	POCosts po 			= null;

	//tabla de hash con los costos por PO
	pocBean.getAllByPOIdCEId(versionId);
	
	//tabla de hash con los costos recurrentes
	pocBean.getAllByPOIdCEIdRecurrent(versionId, 3);	//el 3 indica que son los recurrentes
	
	//tabla de hash con los costos de almacen
	pocBean.getAllByPOIdCEIdWarehouse(versionId);
	
	//costos totales de PO por planta - mes aunque es necesario 
	//	"restarle" el valor de los futuros para mostrarlo en pantalla
	POPlantCostsBean ppoBean	= new POPlantCostsBean();
	ArrayList ppos				= ppoBean.browse(versionId);
	ArrayList posFut			= ppoBean.browse(versionId, misc.FUTURES, true);
	POPlantCosts ppo			= null;
	
	//Hashtable de costos de la po en planta
	Hashtable fullCostPOinPlant	= new Hashtable(ppos.size());
	
	//Hashtable de lo distribuido por PO en Planta
	Hashtable tonsPOinPlant		= new Hashtable(ppos.size());
	
	for (i = 0; i < ppos.size(); i++){
		ppo = (POPlantCosts)ppos.get(i);
		//costo
		fullCostPOinPlant.put(ppo.getPurchaseOrderVersionId() + "|" + ppo.getMonthPeriodVersionId() + "|" + ppo.getPlantId(), new Double(ppo.getAmount()));
		
		//toneladas
		tonsPOinPlant.put(ppo.getPurchaseOrderVersionId() + "|" + ppo.getMonthPeriodVersionId() + "|" + ppo.getPlantId(), new Double(ppo.getTons()));
	}
	
	//Hashtable de costos de futuros de la po en planta
	Hashtable futuresCostPOinPlant	= new Hashtable(posFut.size());
	
	for (i = 0; i < posFut.size(); i++){
		ppo = (POPlantCosts)posFut.get(i);
		futuresCostPOinPlant.put(ppo.getPurchaseOrderVersionId() + "|" + ppo.getMonthPeriodVersionId() + "|" + ppo.getPlantId(), new Double(ppo.getAmount()));
	}
	
	String amount;
	Double dblAmount;
	Double dblAmount2;
	Double dblTons;
	double cornTotal;
	
	DecimalFormat pf = new DecimalFormat("#,##0.0000");

	//ciclo por planta de TODA la tabla
	sql  = "SELECT plant_id, short_name, description ";
	sql += "	FROM plants ";
	
	if (DEBUG) out.println("<br>sql: [" + sql + "]");
	rs = stmt.executeQuery(sql);
	
	int plantId;
	String plantShortName;
	String plantDescription;
	
	
	while (rs.next()){
		plantId				= rs.getInt("plant_id");
		plantShortName		= rs.getString("short_name");
		plantDescription	= rs.getString("description");
		
		aPlantsVersions = pvBean.browse(versionId, plantId);
		
		out.println("<br> Costos para " + plantShortName + " - " + plantDescription);
		out.println("<br>");
%>
        <table border="0" align="center" cellpadding="2" cellspacing="1">
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td colspan="<%=aCostsElements.size()%>" align="center" class="cb_titulocolumna cellBorder">Costos</td>
            <td align="center">&nbsp;</td>
            <td align="center">&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
<%
		//costos recurrentes por cada mes
		for (i = 0; i < aMonthPeriodsVersions.size(); i++){
			out.println("            <td colspan=\"" + aCostsElementsRecurrent.size() + "\" align=\"center\" class=\"cb_titulocolumna cellBorder\">Recurrentes</td>");
			out.println("            <td>&nbsp;</td>");
			out.println("            <td>&nbsp;</td>");
			out.println("            <td>&nbsp;</td>");
		}
%>
            <td>&nbsp;</td>
<%
		//Totales por mes
		for (i = 0; i < aMonthPeriodsVersions.size(); i++){
			mpvDto = (MonthsPeriodsVersions)aMonthPeriodsVersions.get(i);
			out.println("<td colspan=\"3\" align=\"center\" class=\"cb_titulocolumna cellBorder\">" + mpvDto.getShortName() + "</td>");
		}
%>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td align="center" class="cb_titulocolumna cellBorder">Maíz</td>
<%
		for (i = 0; i < aCostsElements.size(); i++) {
			costElementDto = (CostElementDTO)aCostsElements.get(i);
			
			out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">" + costElementDto.getCostElementCode() + "</td>");
		}
%>
            <td align="center" class="cb_titulocolumna cellBorder">Maíz<br>
              Subtotal</td>
            <td align="center" class="cb_titulocolumna cellBorder">Futuros</td>
            <td align="center" class="cb_titulocolumna cellBorder">Total<br>
              Maíz</td>
            <td>&nbsp;</td>
<%
		//ciclo de cada mes para sus costos recurrentes
		for (i = 0; i < aMonthPeriodsVersions.size(); i++){
			mpvDto = (MonthsPeriodsVersions)aMonthPeriodsVersions.get(i);
			
			for (j = 0; j < aCostsElementsRecurrent.size(); j++){
				recurrentCostName = (String) aCostsElementsRecurrent.get(j);
				out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">" + recurrentCostName + "</td>");
			}
			
			out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">Alm.</td>");
			out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">" + mpvDto.getShortName() + "</td>");
			out.println("            <td>&nbsp;</td>");
		}
%>
            <td>&nbsp;</td>
<%
		for (i = 0; i < aMonthPeriodsVersions.size(); i++){
			out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">Maíz</td>");
			out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">Futuros</td>");
			out.println("            <td align=\"center\" class=\"cb_titulocolumna cellBorder\">TOTAL</td>");
		}
%>

          </tr>
<%
		/*
		*	CICLO DE LAS PO
		*
		*/
		
		//PO validas por planta
		filter 				= "plant_id = " + plantId;
		aPODistributions	= pdBean.browse(1, true, filter);
		
		odd = false;
		
		for (i = 0; i < aPOs.size(); i++){
			odd = !odd;
			po = (POCosts)aPOs.get(i);
			
			//determinar si esta po tuvo costos en esta planta
			if (!pdBean.isPOInPlant(po.getPurchaseOrderVersionId(), plantId)){
				continue;
			}
			
			cornTotal = 0;
			
			if (po.getAmount() > 0.0){
				cornTotal	= po.getAmount();
				amount 		= pf.format(po.getAmount());
			} else {
				amount		= misc.NBSP;
			}
			
			out.println("          <tr class=\"" + (odd ? "columnadatoNon" : "columnadatoPar") + "\">");
			out.println("            <td class=\"dw_etiqueta_campo cellBorder\">" + po.getCode() + "</td>");
			out.println("            <td class=\"columnadatoPar\">&nbsp;</td>");
			out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
			
			//obtener
			for (j = 0; j < aCostsElements.size(); j++) {
				costElementDto = (CostElementDTO)aCostsElements.get(j);
				
				dblAmount = pocBean.costPOIdCEId(po.getPurchaseOrderVersionId(), costElementDto.getCostElementCode());
				
				if (dblAmount != null && dblAmount.doubleValue() > 0.0){
					cornTotal	+= dblAmount.doubleValue();
					amount 		= pf.format(dblAmount.doubleValue());
				} else {
					amount		= misc.NBSP;
				}
				
				out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
			}
			
			//Maiz subtotal
			out.println("            <td align=\"right\" class=\"cellBorder\">" + pf.format(cornTotal) + "</td>");
			
			//valor de futuros
			dblAmount = pocBean.costPOIdCEId(po.getPurchaseOrderVersionId(), misc.LOSS_FUTURES);
			
			if (dblAmount != null && dblAmount.doubleValue() > 0.0){
				cornTotal	+= dblAmount.doubleValue();
				amount 		= pf.format(dblAmount.doubleValue());
			} else {
				amount		= misc.NBSP;
			}
			
			out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
			
			//Maiz total
			out.println("            <td align=\"right\" class=\"cellBorder\">" + pf.format(cornTotal) + "</td>");
			
			//espacio
			out.println("            <td class=\"cleanCell\">&nbsp;</td>");
			
			//ciclo de los meses para los costos recurrentes
			for (j = 0; j < aMonthPeriodsVersions.size(); j++){
				mpvDto = (MonthsPeriodsVersions) aMonthPeriodsVersions.get(j);
				
				//tecnicamente debe haber un plant_version_id para cada mes
				pvDto = (PlantsVersionsMonths) aPlantsVersions.get(j);
				
				cornTotal = 0;
				//ciclo por cada elemento de costo recurrente
				for (k = 0; k < aCostsElementsRecurrent.size(); k++){
					recurrentCostName = (String) aCostsElementsRecurrent.get(k);
					
					dblAmount = pocBean.costPOIdCEIdRecurrent(po.getPurchaseOrderVersionId(), recurrentCostName, pvDto.getPlantVersionId());
					
					if (dblAmount != null && dblAmount.doubleValue() > 0.0){
						cornTotal	+= dblAmount.doubleValue();
						amount 		= pf.format(dblAmount.doubleValue());
					} else {
						amount		= misc.NBSP;
					}
					
					out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
				}
				
				//obtener costo de almacen: Entradas, Salidas y Mensualidades
				dblAmount = pocBean.costPOIdCEIdWarehouse(po.getPurchaseOrderVersionId(), pvDto.getPlantVersionId());
				
				if (dblAmount != null && dblAmount.doubleValue() > 0.0){
					cornTotal	+= dblAmount.doubleValue();
					amount 		= pf.format(dblAmount.doubleValue());
				} else {
					amount		= misc.NBSP;
				}
				
				//Almacen
				out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
				
				if (cornTotal > 0.0){
					amount 		= pf.format(cornTotal);
				} else {
					amount		= misc.NBSP;
				}
				
				//Total del mes
				out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
				
				//espacio
				out.println("            <td class=\"cleanCell\">&nbsp;</td>");
			}
	
			//espacio extra
			out.println("            <td class=\"cleanCell\">&nbsp;</td>");
	
			//ciclo de los meses de totales
			for (j = 0; j < aMonthPeriodsVersions.size(); j++){
				mpvDto = (MonthsPeriodsVersions) aMonthPeriodsVersions.get(j);
				
				//tecnicamente debe haber un plant_version_id para cada mes
				pvDto = (PlantsVersionsMonths) aPlantsVersions.get(j);
				
				cornTotal = 0;
				
				//obtener el costo de la PO en la planta - mes
				dblAmount = (Double)fullCostPOinPlant.get(po.getPurchaseOrderVersionId() + "|" + mpvDto.getMonthPeriodVersionId() + "|" + pvDto.getPlantId());
				dblAmount = dblAmount == null ? new Double(0) : dblAmount;
				
				//obtener el costo de futuros de la PO en la planta - mes
				dblAmount2 = (Double)futuresCostPOinPlant.get(po.getPurchaseOrderVersionId() + "|" + mpvDto.getMonthPeriodVersionId() + "|" + pvDto.getPlantId());
				dblAmount2 = dblAmount2 == null ? new Double(0) : dblAmount2;
				
				//obtener el tonelaje de la PO en la planta - mes
				dblTons = (Double)tonsPOinPlant.get(po.getPurchaseOrderVersionId() + "|" + mpvDto.getMonthPeriodVersionId() + "|" + pvDto.getPlantId());
				dblTons = dblTons == null ? new Double(0) : dblTons;
				
				//restar futuros a amount
				dblAmount = new Double(dblAmount.doubleValue() - dblAmount2.doubleValue());
				
				if (dblAmount.doubleValue() > 0.0){
					cornTotal	+= dblAmount.doubleValue() * dblTons.doubleValue();
					amount 		= pf.format(dblAmount.doubleValue() * dblTons.doubleValue());
				} else {
					amount		= misc.NBSP;
				}
				
				//Maíz
				out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
				
				if (dblAmount2.doubleValue() > 0.0){
					cornTotal	+= dblAmount2.doubleValue() * dblTons.doubleValue();
					amount 		= pf.format(dblAmount2.doubleValue() * dblTons.doubleValue());
				} else {
					amount		= misc.NBSP;
				}
				
				//Futuros
				out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
	
				if (cornTotal > 0.0){
					amount 		= pf.format(cornTotal);
				} else {
					amount		= misc.NBSP;
				}
				
				//Total del mes
				out.println("            <td align=\"right\" class=\"cellBorder\">" + amount + "</td>");
			}
	
		}
%>
        </table>
<%
	}
%>
</form>
<%
}catch (Exception e){
	String errorMsg = "";
	errorMsg  = "            <table border=\"0\" cellspacing=\"2\" cellpadding=\"2\">";
	errorMsg += "              <tr>";
	errorMsg += "                <td class=\"errorMsg\">";
	
	errorMsg += "                  Error al procesar el registro";
	errorMsg += "                  <br>Mensaje de error: " + e.getMessage();

	errorMsg += "                </td>";
	errorMsg += "              </tr>";
	errorMsg += "            </table>";
	
	out.println(misc.ERROR + "|" + errorMsg);
} finally {
    try { conn.close(); } catch (Exception e) {}
    try { stmt.close(); } catch (Exception e) {}
    try { rs.close();   } catch (Exception e) {}
}
%>
	    	</td>
	  </tr>
	</table>
</div>